from sqlalchemy import select, asc, text, distinct
from sqlalchemy.future import select

from app.config import db
from app.model.schoolInfo import SchoolInfo, SchoolBrochure, SchoolPlan, SchoolScore
from app.repository.base_repo import BaseRepo
from sqlalchemy.sql.expression import func


class SchoolInfoRepository(BaseRepo):
    model = SchoolInfo

    @staticmethod
    async def find_by_id(school_id: int):
        query = select(SchoolInfo).where(SchoolInfo.id == school_id)
        return (await db.execute(query)).scalar_one_or_none()

    async def find_all_schools(filter_985: bool, filter_211: bool, filter_dfc: bool, filter_imp_country: bool, filter_city: str,
                               school_name: str):
        query = select(SchoolInfo.id, SchoolInfo.school, SchoolInfo.logo_url, SchoolInfo.is_985, SchoolInfo.is_211, SchoolInfo.is_imp_country, SchoolInfo.is_imp_prov,
                       SchoolInfo.is_dfc, SchoolInfo.city_short, SchoolInfo.prov, SchoolInfo.city,
                       SchoolInfo.school_type, SchoolInfo.is_gov, SchoolInfo.attach).order_by(asc(SchoolInfo.id))

        # 添加筛选条件
        if filter_985:
            query = query.where(SchoolInfo.is_985 == 1)

        if filter_211:
            query = query.where(SchoolInfo.is_211 == 1)

        if filter_dfc:
            query = query.where(SchoolInfo.is_dfc == 1)

        if filter_imp_country:
            query = query.where(SchoolInfo.is_imp_country == 1)

        if filter_city:
            query = query.where(SchoolInfo.city_short.in_(filter_city))

        if school_name:
            query = query.where(SchoolInfo.school.ilike(f"%{school_name}%"))
        print(query)

        result = await db.execute(query)
        return result.all()

    @staticmethod
    async def find_school_by_rank(rank_type: str):
        query = select(SchoolInfo.id, SchoolInfo.school, SchoolInfo.logo_url, SchoolInfo.rank).order_by(
            text(f"(rank->>'{rank_type}')::integer ASC"))

        result = await db.execute(query)
        return result.all()

    @staticmethod
    async def get_all_provinces():
        query = select(distinct(SchoolInfo.city_short))
        query = query.order_by(SchoolInfo.city_short)
        result = await db.execute(query)
        provinces = [row[0] for row in result]
        return provinces

    @staticmethod
    async def find_by_id(school_id: int):
        query = select(SchoolInfo).where(SchoolInfo.id == school_id)
        return (await db.execute(query)).scalar_one_or_none()

    @staticmethod
    async def find_titles_by_school(school_id: int):
        query = select(SchoolBrochure.title, SchoolBrochure.id, SchoolBrochure.create_date).where(SchoolBrochure.school == school_id)
        return (await db.execute(query)).all()

    @staticmethod
    async def find_content_by_id(brochure_id: int):
        query = select(SchoolBrochure).where(SchoolBrochure.id == brochure_id)
        return (await db.execute(query)).all()

    @staticmethod
    async def get_plans_by_nature(school_code: str, nature: str == None):
        query = select(SchoolPlan).where(SchoolPlan.school_code == school_code)
        if nature == '1':
            query = query.where(SchoolPlan.nature == "文科")
        else:
            query = query.where(SchoolPlan.nature == "理科")
        return (await db.execute(query)).all()

    @staticmethod
    async def get_score_by_id(id: int):
        query = select(SchoolScore).where(SchoolScore.id == id)
        return (await db.execute(query)).all()
